package com.fs.files.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import com.fs.core.util.StringUtil;
import com.fs.files.pojo.excel.CellPojo;
import com.fs.files.pojo.excel.ExcelPojo;
import com.fs.files.pojo.excel.RowPojo;
import com.fs.files.pojo.excel.SheetPojo;
import com.fs.files.service.ExcelService;
import com.fs.files.util.ExcelUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.stereotype.Service;

import com.fasterxml.jackson.databind.ObjectMapper;
/**
 * @version poi 3.17
 * @author pangps
 */
@Service
public class ExcelServiceImpl implements ExcelService {

	//公式单元格
	private static FormulaEvaluator evaluator;
	
	@Override
	public ExcelPojo readExcel(String path) throws IOException {
		InputStream input = null;//输入流
		ExcelPojo excelPojo = new ExcelPojo();	//excel对象
		try {
			input = new FileInputStream(path);
			// 创建Excel工作簿对象,poi 3.15版本以后不用管它是xls还是xlsx
			Workbook wb = WorkbookFactory.create(input);
			String fileName = getFileName(path);
			excelPojo.setFileName(fileName);
			//公式单元格
			evaluator = wb.getCreationHelper().createFormulaEvaluator();
			// 取得sheet的数目
			int sheetCount = wb.getNumberOfSheets();
			//sheet对象集合
			List<SheetPojo> sheetPojoList = new ArrayList<SheetPojo>();
			for (int i = 0; i < sheetCount; i++) {
				// 判断sheet是否隐藏,如果隐藏则不读取
				boolean flag = wb.isSheetHidden(i);
				if (flag) {
					continue;
				}
				// 根据index取得sheet对象
				Sheet sheet = wb.getSheetAt(i);
				// 获取sheet名称
				String sheetName = sheet.getSheetName();
				//sheet对象
				SheetPojo sheetPojo = new SheetPojo();
				sheetPojo.setIndex(String.valueOf(i));
				sheetPojo.setSheetName(sheetName);
				sheetPojoList.add(sheetPojo);
				// 取得有效的行数
				int rowCount = sheet.getLastRowNum();
				List<RowPojo> rowPojoList = new ArrayList<RowPojo>();
				for (int k = 0; k <= rowCount; k++) {
					// 获取一行
					Row row = sheet.getRow(k);
					if(null == row){
						continue;
					}
					//row对象
					RowPojo rowPojo = new RowPojo();
					rowPojo.setIndex(String.valueOf(k));
					// 取得一行的有效单元格个数
					int cellCount = row.getLastCellNum();
					List<CellPojo> cellPojoList = new ArrayList<CellPojo>();
					for (int m = 0; m < cellCount; m++) {
						Cell cell = row.getCell(m);
						if(null != cell){
							String value = ExcelUtil.getCellStringValue(cell, evaluator);
							//全角转半角
							value = StringUtil.full2Half(value);
							//去除所有空格
							value = value.replaceAll("\\s*", "");
//							if("".equals(value)){
//								continue;
//							}
							CellPojo cellPojo = new CellPojo(String.valueOf(m), value);
							cellPojoList.add(cellPojo);
						}
					}
					if(!cellPojoList.isEmpty()){
						rowPojo.setCells(cellPojoList);
						rowPojoList.add(rowPojo);
					}
				}
				sheetPojo.setRows(rowPojoList);
			}
			excelPojo.setSheets(sheetPojoList);
			// 关闭
			wb.close();
			input.close();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
            if(input != null) {
                input.close();
            }
        }

		return excelPojo;
	}
	
	
	@Override
	public String readExcelToJsonString(String path) throws IOException{
		ObjectMapper mapper = new ObjectMapper();
		ExcelPojo excelPojo = readExcel(path);
		String json = mapper.writeValueAsString(excelPojo);
		return json;
	}

	@Override
	public String getFileName(String path) {
		path = path.trim();
		File file1 = new File(path);
		String fileName = file1.getName();
		return fileName;
	}

	@Override
	public String getFileType(String path) {
		return null;
	}

	@Override
	public ExcelPojo uploadExcel(InputStream input, String fileName) throws IOException {
		ExcelPojo excelPojo = new ExcelPojo();	//excel对象
		try {
			// 创建Excel工作簿对象,poi 3.15版本以后不用管它是xls还是xlsx
			Workbook wb = WorkbookFactory.create(input);
			excelPojo.setFileName(fileName);
			//公式单元格
			evaluator = wb.getCreationHelper().createFormulaEvaluator();
			// 取得sheet的数目
			int sheetCount = wb.getNumberOfSheets();
			//sheet对象集合
			List<SheetPojo> sheetPojoList = new ArrayList<SheetPojo>();
			for (int i = 0; i < sheetCount; i++) {
				// 判断sheet是否隐藏,如果隐藏则不读取
				boolean flag = wb.isSheetHidden(i);
				if (flag) {
					continue;
				}
				// 根据index取得sheet对象
				Sheet sheet = wb.getSheetAt(i);
				// 获取sheet名称
				String sheetName = sheet.getSheetName();
				//sheet对象
				SheetPojo sheetPojo = new SheetPojo();
				sheetPojo.setIndex(String.valueOf(i));
				sheetPojo.setSheetName(sheetName);
				sheetPojoList.add(sheetPojo);
				// 取得有效的行数
				int rowCount = sheet.getLastRowNum();
				List<RowPojo> rowPojoList = new ArrayList<RowPojo>();
				for (int k = 0; k <= rowCount; k++) {
					// 获取一行
					Row row = sheet.getRow(k);
					if(null == row){
						continue;
					}
					//row对象
					RowPojo rowPojo = new RowPojo();
					rowPojo.setIndex(String.valueOf(k));
					// 取得一行的有效单元格个数
					int cellCount = row.getLastCellNum();
					List<CellPojo> cellPojoList = new ArrayList<CellPojo>();
					for (int m = 0; m < cellCount; m++) {
						Cell cell = row.getCell(m);
						if(null != cell){
							String value = ExcelUtil.getCellStringValue(cell, evaluator);
							//全角转半角
							value = StringUtil.full2Half(value);
							//去除所有空格
							value = value.replaceAll("\\s*", "");
//							if("".equals(value)){
//								continue;
//							}
							CellPojo cellPojo = new CellPojo(String.valueOf(m), value);
							cellPojoList.add(cellPojo);
						}
					}
					if(!cellPojoList.isEmpty()){
						rowPojo.setCells(cellPojoList);
						rowPojoList.add(rowPojo);
					}
				}
				sheetPojo.setRows(rowPojoList);
			}
			excelPojo.setSheets(sheetPojoList);
			// 关闭
			wb.close();
			input.close();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
            if(input != null) {
                input.close();
            }
        }
		return excelPojo;
	}

	@Override
	public ExcelPojo readExcelByFile(InputStream stream) throws IOException {
		InputStream input = stream;//输入流
		ExcelPojo excelPojo = new ExcelPojo();	//excel对象
		try {
			// 创建Excel工作簿对象,poi 3.15版本以后不用管它是xls还是xlsx
			Workbook wb = WorkbookFactory.create(input);
			String fileName = getFileName("");
			excelPojo.setFileName(fileName);
			//公式单元格
			evaluator = wb.getCreationHelper().createFormulaEvaluator();
			// 取得sheet的数目
			int sheetCount = wb.getNumberOfSheets();
			//sheet对象集合
			List<SheetPojo> sheetPojoList = new ArrayList<SheetPojo>();
			for (int i = 0; i < sheetCount; i++) {
				// 判断sheet是否隐藏,如果隐藏则不读取
				boolean flag = wb.isSheetHidden(i);
				if (flag) {
					continue;
				}
				// 根据index取得sheet对象
				Sheet sheet = wb.getSheetAt(i);
				// 获取sheet名称
				String sheetName = sheet.getSheetName();
				//sheet对象
				SheetPojo sheetPojo = new SheetPojo();
				sheetPojo.setIndex(String.valueOf(i));
				sheetPojo.setSheetName(sheetName);
				sheetPojoList.add(sheetPojo);
				// 取得有效的行数
				int rowCount = sheet.getLastRowNum();
				List<RowPojo> rowPojoList = new ArrayList<RowPojo>();
				for (int k = 0; k <= rowCount; k++) {
					// 获取一行
					Row row = sheet.getRow(k);
					if(null == row){
						continue;
					}
					//row对象
					RowPojo rowPojo = new RowPojo();
					rowPojo.setIndex(String.valueOf(k));
					// 取得一行的有效单元格个数
					int cellCount = row.getLastCellNum();
					List<CellPojo> cellPojoList = new ArrayList<CellPojo>();
					for (int m = 0; m < cellCount; m++) {
						Cell cell = row.getCell(m);
						if(null != cell){
							String value = ExcelUtil.getCellStringValue(cell, evaluator);
							//全角转半角
							value = StringUtil.full2Half(value);
							//去除所有空格
							value = value.replaceAll("\\s*", "");
//							if("".equals(value)){
//								continue;
//							}
							CellPojo cellPojo = new CellPojo(String.valueOf(m), value);
							cellPojoList.add(cellPojo);
						}
					}
					if(!cellPojoList.isEmpty()){
						rowPojo.setCells(cellPojoList);
						rowPojoList.add(rowPojo);
					}
				}
				sheetPojo.setRows(rowPojoList);
			}
			excelPojo.setSheets(sheetPojoList);
			// 关闭
			wb.close();
			input.close();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
            if(input != null) {
                input.close();
            }
        }

		return excelPojo;
	}
	
}
